[Repost-ish] Impossibly slow queries, Tables indexed, How can I speed it up?

Posted by colorfulgrayscale on Stack Overflow See other posts from Stack Overflow or by colorfulgrayscale
Published on 2010-04-17T04:15:25Z Indexed on 2010/04/17 4:23 UTC
Read the original article Hit count: 442

Filed under:
|
|
|
|

Hi guys, I posted a little earlier on here at http://stackoverflow.com/questions/2656837/query-results-taking-too-long-on-200k-database-speed-up-tips asking about slow executing SQL queries. I was told to index the columns; I did. and its still slow (slow as in, i never see the results, both mysql and sqlite freeze up on query). Help would be greatly appreciated.

Here is the SQL

SELECT
  equipment.`unitID`      AS `equipment_unitID`,
  equipment.`fleetCode`   AS `equipment_fleetCode`,
  equipment.type          AS equipment_type,
  equipment.tiremap       AS equipment_tiremap,
  tiremap.`TireID`        AS `tiremap_TireID`,
  tiremap.`WorkMap`       AS `tiremap_WorkMap`,
  tiremap.`Position`      AS `tiremap_Position`,
  tiremap.`DepthMap`      AS `tiremap_DepthMap`,
  tiremap.timestamp       AS tiremap_timestamp,
  workreference.`aMap`    AS `workreference_aMap`,
  workreference.`bMap`    AS `workreference_bMap`,
  tirework.`RO`           AS `tirework_RO`,
  tirework.location       AS tirework_location,
  tirework.mileage        AS tirework_mileage,
  tirework.`mechanicCode` AS `tirework_mechanicCode`,
  tirework.`partNumber`   AS `tirework_partNumber`,
  tirework.`historyID`    AS `tirework_historyID`,
  tirework.workmap        AS tirework_workmap,
  tirework.timestamp      AS tirework_timestamp
FROM equipment,
  tiremap,
  workreference,
  tirework
WHERE equipment.tiremap = tiremap.`TireID`
    AND tiremap.`WorkMap` = workreference.`aMap`
    AND workreference.`bMap` = tirework.workmap
LIMIT 5

and here is the EXPLAIN for it

id  select_type  table          type    possible_keys                     key        key_len  ref                      rows   Extra  
1   SIMPLE       equipment      ALL     tiremap                                                                        14079         
1   SIMPLE       tiremap        ref     PRIMARY,WorkMap,TireID,WorkMap_2  PRIMARY    52       tire.equipment.tiremap   3             
1   SIMPLE       workreference  ref     aMap,bMap                         aMap       52       tire.tiremap.WorkMap     1             
1   SIMPLE       tirework       eq_ref  NewIndex1                         NewIndex1  52       tire.workreference.bMap  1             

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql